package com.qm.console.service.bx.impl;

import com.qm.console.component.exception.BusinessException;
import com.qm.console.component.util.DateUtil;
import com.qm.console.controller.dto.ResultEnum;
import com.qm.console.controller.vo.comm.query.TimeOffsetQuery;
import com.qm.console.dao.mapper.BxAtomFieldDOMapper;
import com.qm.console.dao.mapper.BxDisplayFieldDOMapper;
import com.qm.console.dao.mapper.BxJobDOMapper;
import com.qm.console.dao.model.BxDisplayFieldDO;
import com.qm.console.dao.model.BxJobDO;
import com.qm.console.pubdef.dto.bx.BxColumnDTO;
import com.qm.console.pubdef.dto.bx.BxDisplayFieldDTO;
import com.qm.console.pubdef.dto.bx.BxJobDTO;
import com.qm.console.service.bx.BxDataService;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.stream.Collectors;

import lombok.extern.slf4j.Slf4j;

/**
 * //                       _oo0oo_
 * //                      o8888888o
 * //                      88" . "88
 * //                      (| -_- |)
 * //                      0\  =  /0
 * //                    ___/`---'\___
 * //                  .' \\|     |// '.
 * //                 / \\|||  :  |||// \
 * //                / _||||| -:- |||||- \
 * //               |   | \\\  -  /// |   |
 * //               | \_|  ''\---/''  |_/ |
 * //               \  .-\__  '-'  ___/-. /
 * //             ___'. .'  /--.--\  `. .'___
 * //          ."" '<  `.___\_<|>_/___.' >' "".
 * //         | | :  `- \`.;`\ _ /`;.`/ - ` : | |
 * //         \  \ `_.   \_ __\ /__ _/   .-` /  /
 * //     =====`-.____`.___ \_____/___.-`___.-'=====
 * //                       `=---='
 * //
 * //
 * //     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 * //
 * //               佛祖保佑         永无BUG
 * <p>
 * com.qm.console.service.bx.impl
 * Created on 2021/1/12.
 */
@Service
@Slf4j
public class BxDataServiceImpl implements BxDataService {


    @Autowired
    private BxJobDOMapper bxJobDOMapper;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private BxDisplayFieldDOMapper bxDisplayFieldDOMapper;
    @Autowired
    private BxAtomFieldDOMapper bxAtomFieldDOMapper;
    @Autowired
    private DateUtil dateUtil;

    @Override
    public Map<String, Object> getJobFilters(Integer id) {
        BxJobDO job = bxJobDOMapper.selectByPrimaryKey(id);
        if (job == null) {
            throw new BusinessException(ResultEnum.BX_JOB_NOT_EXISTS);
        }
        String rawGroupers = job.getGroupers();
        if (StringUtils.isEmpty(rawGroupers)) {
            return new HashMap<>();
        }
        String[] groupers = rawGroupers.split(",");
        Map<String, Object> result = new HashMap<>();
        for (String grouper : groupers) {
            List<Map<String, Object>> groupValues = jdbcTemplate.queryForList(String.format("select distinct(%s) as %s from job_%s", grouper, grouper, id));
            List<String> values = groupValues.stream().map(item -> item.get(grouper)).map(Object::toString).collect(Collectors.toList());
            result.put(grouper, values);
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getJobData(Integer id, Map<String, String> requestParams) {

        BxJobDO jobDO = bxJobDOMapper.selectByPrimaryKey(id);
        if (StringUtils.isEmpty(jobDO.getDisplayFields())) {
            return Collections.emptyList();
        }
        String tableName = String.format("job_%s", id);
        List<BxDisplayFieldDTO> displayFields = BxJobDTO.parseDisplayFields(jobDO.getDisplayFields(), bxDisplayFieldDOMapper, bxAtomFieldDOMapper);
        SQL sql = new SQL();
        for (BxDisplayFieldDTO displayField : displayFields) {
            String fieldQuery = displayField.getQuery();
            fieldQuery = fieldQuery.replace("$job_name", tableName);
            fieldQuery = String.format("(%s) as %s", fieldQuery, displayField.getFieldName());
            sql.SELECT(fieldQuery);
        }
        String sqlString = formatJobQuery(requestParams, tableName, sql, true);
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sqlString);
        for (Map<String, Object> item : result) {
            for (Map.Entry<String, Object> entry : item.entrySet()) {
                if (entry.getValue() instanceof Date) {
                    if (entry.getKey().toLowerCase(Locale.ROOT).contains("date")) {
                        item.put(entry.getKey(), dateUtil.dateToStr((Date) entry.getValue()));
                    } else {
                        item.put(entry.getKey(), dateUtil.dateToTimestampStr((Date) entry.getValue()));
                    }
                }
            }
        }
        return result;
    }

    @Override
    public int getJobDataCount(Integer id, Map<String, String> requestParams) {
        BxJobDO jobDO = bxJobDOMapper.selectByPrimaryKey(id);
        String tableName = String.format("job_%s", id);
        SQL sql = new SQL() {{
            SELECT("1");
        }};
        String sqlString = formatJobQuery(requestParams, tableName, sql, false);
        String countSql = String.format("select count(*) as c from (%s) as t", sqlString);
        Map<String, Object> result = jdbcTemplate.queryForMap(countSql);
        return Integer.parseInt(result.get("c").toString());
    }


    private String formatJobQuery(Map<String, String> requestParams, String tableName, SQL sql, boolean needLimitOffset) {
        List<String> groupers = new ArrayList<>();
        groupers.add("target_date");
        String offset = null;
        String limit = null;
        String startTime = null;
        String endTime = null;
        if (requestParams != null) {
            offset = requestParams.remove("offset");
            limit = requestParams.remove("limit");
            startTime = requestParams.remove("startTime");
            endTime = requestParams.remove("endTime");
            List<String> temp = new ArrayList<>();
            for (Map.Entry<String, String> entry : requestParams.entrySet()) {
                if (!StringUtils.isEmpty(entry.getValue())) {
                    temp.add(entry.getKey());
                }
            }
            groupers.addAll(temp);
            sql.SELECT("target_date");
        }
        sql.FROM(tableName);
        if (startTime != null) {
            sql.WHERE(String.format("target_date >= '%s'", startTime));
        }
        if (endTime != null) {
            sql.WHERE(String.format("target_date < '%s'", endTime));
        }
        // come on know
        for (String grouper : groupers) {
            assert requestParams != null;
            if (requestParams.containsKey(grouper)) {
                sql.WHERE(String.format("`%s` = '%s'", grouper, requestParams.get(grouper)));
            }
        }
        String[] groupBy = new String[groupers.size()];
        groupers.toArray(groupBy);
        sql.GROUP_BY(groupBy);
        String sqlString = sql.toString();
        if (needLimitOffset) {
            if (limit != null) {
                sqlString += " limit " + limit;
            }
            if (offset != null) {
                sqlString += " offset " + offset;
            }
        }
        return sqlString;
    }

    @Override
    public List<BxColumnDTO> getJobDataColumns(Integer id) {
        BxJobDO jobDO = bxJobDOMapper.selectByPrimaryKey(id);
        if (jobDO == null) {
            throw new BusinessException(ResultEnum.BX_JOB_NOT_EXISTS);
        }
        List<BxDisplayFieldDTO> displayFields = BxJobDTO.parseDisplayFields(jobDO.getDisplayFields(), bxDisplayFieldDOMapper, bxAtomFieldDOMapper);
        List<BxColumnDTO> result = displayFields.stream().map(item -> {
            BxColumnDTO dto = new BxColumnDTO();
            dto.setFieldName(item.getFieldName());
            dto.setDesc(StringUtils.isEmpty(item.getDesc()) ? item.getFieldName() : item.getDesc());
            return dto;
        }).collect(Collectors.toList());
        BxColumnDTO targetDate = new BxColumnDTO();
        targetDate.setDesc("日期");
        targetDate.setFieldName("target_date");
        result.add(0, targetDate);
        return result;
    }

    @Override
    public List<Map<String, Object>> getJobRawData(Integer id, TimeOffsetQuery query) {
        SQL sql = new SQL() {{
            SELECT("*");
            FROM("job_" + id);
        }};
        if (query.getStartTime() != null) {
            sql.WHERE("target_date >= ?");
        }
        if (query.getEndTime() != null) {
            sql.WHERE("target_date <= ?");
        }
        String sqlString = sql.toString();
        if (query.getLimit() != null) {
            sqlString += " limit " + query.getLimit();
        }
        if (query.getOffset() != null) {
            sqlString += " offset " + query.getOffset();
        }
        return jdbcTemplate.queryForList(sqlString);
    }

    @Override
    public int getJobRawDataCount(Integer id, TimeOffsetQuery query) {
        SQL sql = new SQL() {{
            SELECT("1");
            FROM("job_" + id);
        }};
        if (query.getStartTime() != null) {
            sql.WHERE("target_date >= ?");
        }
        if (query.getEndTime() != null) {
            sql.WHERE("target_date <= ?");
        }
        String sqlString = sql.toString();
        String countSql = String.format("select count(*) as c from (%s) as t", sqlString);
        Map<String, Object> result = jdbcTemplate.queryForMap(countSql);
        return Integer.parseInt(result.get("c").toString());
    }

    @Override
    public List<BxColumnDTO> getJobRawDataColumns(Integer id) {
        return null;
    }
}
